package com.tool.builder.BuildHelper;


import com.tool.bean.Constants;
import com.tool.bean.FieldInfo;
import com.tool.bean.TableInfo;
import com.tool.utils.PropertiesUtils;
import com.tool.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 获取各表、各字段信息
 */
public class BuildTable {

    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private static Connection  conn = null;

    private static String SQL_SHOW_TABLE_STATUS = "show table status";
    private static String SQL_SHOW_TABLE_FIELDS = "show full fields from %s";
    private static String SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getString("db.driver.name");
        String url = PropertiesUtils.getString("db.url");
        String username = PropertiesUtils.getString("db.username");
        String password = PropertiesUtils.getString("db.password");

        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
           logger.error("数据库连接失败", e);
        }
    }

    // 读取库中表信息
    public static List<TableInfo> getTable() {
        PreparedStatement ps = null;
        ResultSet tableResult = null;

        List<TableInfo> tableInfoList = new ArrayList<>();

        try {
            ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tableResult = ps.executeQuery();
            while(tableResult.next()) {
                String tableName = tableResult.getString("name");
                String comment = tableResult.getString("comment");

                String beanName = tableName;
                if(Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(beanName.indexOf("_") + 1);
                }

                beanName = processFiled(beanName, true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);

                readFieldInfo(tableInfo);
                getKeyIndexInfo(tableInfo);
                tableInfoList.add(tableInfo);
            }

        }   catch (Exception e) {
            logger.error("读取表失败");
        }   finally {
            if(tableResult != null) {
                try {
                    tableResult.close();
                }   catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    ps.close();
                }   catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableInfoList;
    }

    // 读取表字段信息
    private static void readFieldInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet tableResult = null;

        List<FieldInfo> fieldInfoList = new ArrayList<>();

        Boolean haveDateTime = false;
        Boolean haveDate = false;
        Boolean haveBigDecimal = false;
        Boolean haveAutoIncrement = false;

        try {
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS, tableInfo.getTableName()));
            tableResult = ps.executeQuery();
            while(tableResult.next()) {
                String field = tableResult.getString("field");
                String type = tableResult.getString("type");
                String extra = tableResult.getString("extra");
                String comment = tableResult.getString("comment");
                String key = tableResult.getString("key");

                if(type.indexOf("(") > 0) type = type.substring(0, type.indexOf("("));
                String propertyName = processFiled(field, false);

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfoList.add(fieldInfo);

                fieldInfo.setFieldName(field);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra) ? true : false);
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));

                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type) ) {
                    haveDateTime = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
                    haveDate = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES, type)) {
                    haveBigDecimal = true;
                }
                if(fieldInfo.getAutoIncrement()) {
                    haveAutoIncrement = true;
                }
                if(key.equalsIgnoreCase("pri")) {
                    fieldInfo.setKey(fieldInfo.getPropertyName());
                }
            }
            tableInfo.setHaveDateTime(haveDateTime);
            tableInfo.setHaveDate(haveDate);
            tableInfo.setHaveBigDecimal(haveBigDecimal);
            tableInfo.setHaveAutoIncrement(haveAutoIncrement);
            tableInfo.setFieldList(fieldInfoList);

        }   catch (Exception e) {
            logger.error("读取表字段失败");
        }   finally {
            if(tableResult != null) {
                try {
                    tableResult.close();
                }   catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    ps.close();
                }   catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 读取索引信息
    private static List<FieldInfo> getKeyIndexInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet tableResult = null;

        List<FieldInfo> fieldInfoList = new ArrayList<>();
        try {
            Map<String, FieldInfo> tempMap = new HashMap<>();
            for(FieldInfo fieldInfo : tableInfo.getFieldList()) {
                tempMap.put(fieldInfo.getFieldName(), fieldInfo);
            }

            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName()));
            tableResult = ps.executeQuery();
            while(tableResult.next()) {
                String keyName = tableResult.getString("key_name");
                Integer nonUnique = tableResult.getInt("non_unique");
                String columnName = tableResult.getString("column_name");

                if(nonUnique == 1) continue;
                List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if(keyFieldList == null) {
                    keyFieldList = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName, keyFieldList);
                }

                keyFieldList.add(tempMap.get(columnName));
            }

        }   catch (Exception e) {
            logger.error("读取索引失败");
        }   finally {
            if(tableResult != null) {
                try {
                    tableResult.close();
                }   catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    ps.close();
                }   catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return fieldInfoList;
    }

    // 下滑线转化为驼峰命名,upperCaseFirstLetter属性用于判断第一个是否大写，表名需要，属性不需要
    private static String processFiled(String field, Boolean upperCaseFirstLetter) {
        StringBuilder sb = new StringBuilder();
        String[] fields = field.split("_");
        sb.append(upperCaseFirstLetter ? StringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);

        for(int i = 1; i < fields.length; i++) {
            sb.append(StringUtils.upperCaseFirstLetter(fields[i]));
        }

        return sb.toString();
    }

    // 将数据库类型转化为对应的Java类型
    private static String processJavaType(String type) {
        if(ArrayUtils.contains(Constants.SQL_INTEGER_TYPES, type)) return "Integer";
        else if(ArrayUtils.contains(Constants.SQL_LONG_TYPES, type)) return "Long";
        else if(ArrayUtils.contains(Constants.SQL_STRING_TYPES, type)) return "String";
        else if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)
        || ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) return "Date";
        else if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES, type)) return "BigDecimal";
        else return "Integer";
    }

}
